package uniLearn.control;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import uniLearn.data.*;

import java.io.File;
import java.math.BigDecimal;
import java.sql.*;

import org.apache.commons.io.FileUtils;

public class AssignmentsControl extends DataControl {
	//define
	//SQL String
	//assignment collector
	private static String SQL_GET_ASSIGNMENT_BY_UNITS = "SELECT `id`, `name`, `unit_id`, `deadline`, `groupSize`, `fullMark`, `information`, `marking`, `submission` FROM  `assignment_collector` WHERE  `unit_id` IN (?);";
	private static String SQL_GET_ASSIGNMENT = "SELECT `name`, `unit_id`, `deadline`, `groupSize`, `fullMark`, `information`, `marking`, `submission` FROM  `assignment_collector` WHERE  `id` = ?;";
	private static String SQL_CREATE_ASSIGNMENT = "INSERT INTO `assignment_collector` (`name`, `unit_id`, `deadline`, `groupSize`, `fullMark`, `information`, `marking`, `submission`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
	private static String SQL_CREATE_SUBMIT_ASSIGNMENT = "INSERT INTO `submit_assignment` (`ac_id`, `submitBy`, `submitTime`, `fileName`) "+
			"SELECT ? AS `ac_id`, `id` AS `submitBy`, NOW() AS `submitTime`, '' AS `fileName` "+
			"FROM `users` LEFT JOIN `users_unit` ON `user_id` = `id` "+
			"WHERE `userType` = 'student' AND `unit_id` = ?;"; //1 = ac_id, 2 = unit_id
	private static String SQL_UPDATE_ASSIGNMENT = "UPDATE `assignment_collector` SET `name` = ?, `deadline` = ?, `groupSize` = ?, `fullMark` = ?, `information` = ?, `marking` = ?, `submission` = ? WHERE `id` = ?;";
	//marking scheme
	private static String SQL_CREATE_DEFAULT_MS = "INSERT INTO `marking_scheme` (`ac_id`, `msOrder`, `name`, `criteria`, `mark`) VALUES (?, '1', 'Total Mark', 'This is the default marking scheme for assignment collector.', ?);";
	private static String SQL_UPDATE_MS = "INSERT INTO `marking_scheme` (`ac_id`, `msOrder`, `name`, `criteria`, `mark`) VALUES (?, ?, ?, ?, ?) "+
			"ON DUPLICATE KEY UPDATE `name`= VALUES(`name`), `criteria`= VALUES(`criteria`), `mark` = VALUES(`mark`);";
	private static String SQL_DELETE_UNUSED_MS = "DELETE FROM `marking_scheme` WHERE `ac_id` = ? AND `msOrder` > ?;";
	//assignment material
	private static String SQL_GET_AM = "SELECT `amOrder`, `name`, `filename`, `information` FROM `assignment_material` WHERE `ac_id` = ?";
	private static String SQL_GET_AM_PATH = "SELECT `filename` FROM `assignment_material` WHERE `ac_id` = ? AND `amOrder` = ?";
	private static String SQL_UPDATE_AM = "INSERT INTO `assignment_material` ( `ac_id`, `amOrder`, `name`, `filename`, `information`) VALUES (?, ?, ?, ?, ?)"+
			"ON DUPLICATE KEY UPDATE `name`= VALUES(`name`), `information` = VALUES(`information`);";
	private static String SQL_UPDATE_AM_FILE = "UPDATE `assignment_material` SET `filename` = ? WHERE `ac_id` = ? AND `amOrder` = ?;";
	private static String SQL_GET_UNUSED_FILE = "SELECT `filename` FROM `assignment_material` WHERE `ac_id` = ? AND `amOrder` NOT IN (?) GROUP BY `filename`";
	//private static String SQL_DELETE_UNUSED_AM = "DELETE FROM `assignment_material` WHERE `ac_id` = ? AND `amOrder` NOT IN (?);";
	private static String SQL_DELETE_ALL_AM = "DELETE FROM `assignment_material` WHERE `ac_id` = ?;";
	
	//dir path
	private static String MATERIAL_DIR_PATH = ROOT_DIR_PATH + "\\material";
	
	//constructor
	public AssignmentsControl() {

	}

	/**
	 * @return the ATERIAL_DIR_PATH
	 */
	public static String getMaterialPath() {
		return MATERIAL_DIR_PATH;
	}

	/**
	 * @param acId The ID of assignment collector
	 * @return The Assignment collector from database
	 * @throws Exception
	 */
	public AssignmentCollector getAssignment(int acId) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		
		AssignmentCollector ac = null;
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_ASSIGNMENT);
		//set parameter
		preStmt.setInt(1, acId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		if(rs.next()) {
			String name = rs.getString("name");
			String unitID = rs.getString("unit_id");
			Date deadline = new Date(rs.getTimestamp("deadline").getTime());
			int groupSize = rs.getInt("groupSize");
			BigDecimal fullMark = rs.getBigDecimal("fullMark");
			String info = rs.getString("information");
			boolean isSubmission = rs.getBoolean("submission");
			boolean isMarking = rs.getBoolean("marking");
			
			ac = new AssignmentCollector(acId, name, unitID, deadline, groupSize);
			ac.setInfo(info);
			ac.setFullMark(fullMark);
			ac.setMarking(isMarking);
			ac.setSubmission(isSubmission);
		}
		else {
			rs.close();
			preStmt.close();
			throw new Exception("Can't Find Assignment");
		}
		rs.close();
		preStmt.close();
		
		return ac;
	}
	
	/**
	 * @param targetUnit The unit of the user select
	 * @return a list of assignment collector that include in the units
	 * @throws Exception
	 */
	public ArrayList<AssignmentCollector> getAssignments(String ... targetUnit) throws Exception {
		
		if(targetUnit == null || targetUnit.length < 1)
			throw new Exception("Incorrect Units input");
		
		ArrayList<AssignmentCollector> acList = new ArrayList<AssignmentCollector>();
		//Get from database
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(regenSQL(SQL_GET_ASSIGNMENT_BY_UNITS, targetUnit.length));
		//set parameter
		for(int i = 0; i < targetUnit.length; i++)
			preStmt.setString((i + 1), targetUnit[i]);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		while(rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String unitID = rs.getString("unit_id");
			Date deadline = new Date(rs.getTimestamp("deadline").getTime());
			int groupSize = rs.getInt("groupSize");
			BigDecimal fullMark = rs.getBigDecimal("fullMark");
			String info = rs.getString("information");
			boolean isSubmission = rs.getBoolean("submission");
			boolean isMarking = rs.getBoolean("marking");
			
			AssignmentCollector ac = new AssignmentCollector(id, name, unitID, deadline, groupSize);
			ac.setInfo(info);
			ac.setFullMark(fullMark);
			ac.setMarking(isMarking);
			ac.setSubmission(isSubmission);
			acList.add(ac);
		}
		rs.close();
		preStmt.close();
		
		return acList;
	}
		
	/**
	 * @param ac The new Assignment Collector
	 * @return the new id of the assignment collector
	 * @throws Exception
	 */
	public int createNewAssignment(AssignmentCollector ac) throws Exception {
		if(checkLength(ac.getName(), AssignmentCollector.NAME_MAX_LENGTH))
			throw new Exception("Incorrect Assignment Name");
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_CREATE_ASSIGNMENT, PreparedStatement.RETURN_GENERATED_KEYS);
		//set parameter
		preStmt.setString(1, ac.getName());
		preStmt.setString(2, ac.getUnitID());
		preStmt.setTimestamp(3, new Timestamp(ac.getDeadline().getTime()));
		preStmt.setInt(4, ac.getGroupSize());
		preStmt.setBigDecimal(5, ac.getFullMark());
		preStmt.setString(6, ac.getInfo());
		preStmt.setBoolean(7, ac.isMarking());
		preStmt.setBoolean(8, ac.isSubmission());
		
		int affRow = preStmt.executeUpdate();
		if(affRow < 1)
			throw new Exception("Can't Create Assignment Collector");
		
		ResultSet rs = preStmt.getGeneratedKeys();
		int acId = 0;
		if(rs.next()) {
			acId = rs.getInt(1);
		}
		else {
			throw new Exception("Can't get back the Assignment Control ID");
		}
		
		preStmt.close();
		rs.close();
		
		//Insert submit assignment
		preStmt = this.dbCon.preStmt(SQL_CREATE_SUBMIT_ASSIGNMENT);
		//set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, ac.getUnitID());
		affRow = preStmt.executeUpdate();
		preStmt.close();
		
		if(affRow < 1)
			throw new Exception("No submit assignment Created");
		
		//Insert a default marking scheme
		preStmt = this.dbCon.preStmt(SQL_CREATE_DEFAULT_MS);
		preStmt.setInt(1, acId);
		preStmt.setBigDecimal(2, ac.getFullMark());
		affRow = preStmt.executeUpdate();
		preStmt.close();
		
		if(affRow<1)
			throw new Exception("No Default Marking Scheme Created");
		
		return acId;
	}
	
	public void updateAssignment(AssignmentCollector ac) throws Exception {
		if(checkLength(ac.getName(), AssignmentCollector.NAME_MAX_LENGTH))
			throw new Exception("Incorrect Assignment Name");
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATE_ASSIGNMENT);
		//set parameter
		preStmt.setString(1, ac.getName());
		preStmt.setTimestamp(2, new Timestamp(ac.getDeadline().getTime()));
		preStmt.setInt(3, ac.getGroupSize());
		preStmt.setBigDecimal(4, ac.getFullMark());
		preStmt.setString(5, ac.getInfo());
		preStmt.setBoolean(6, ac.isMarking());
		preStmt.setBoolean(7, ac.isSubmission());
		preStmt.setInt(8, ac.getId());
		
		int affRow = preStmt.executeUpdate();
		preStmt.close();
		
		if(affRow < 1)
			throw new Exception("Can't Update Assignment Collector");
	}
	
	public void updateMarkingScheme(ArrayList<MarkingScheme> msList) throws Exception {
		if(msList == null || msList.isEmpty())
			throw new Exception("Incorrect marking scheme list");
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATE_MS);
		preStmt.setInt(1, msList.get(0).getAcId());
		
		Iterator<MarkingScheme> mslI = msList.iterator();
		int maxOrder = 0;
		String errMsg = "";
		while(mslI.hasNext()) {
			MarkingScheme ms = mslI.next();
			preStmt.setInt(2, ms.getOrder());
			preStmt.setString(3, ms.getName());
			preStmt.setString(4, ms.getCriteria());
			preStmt.setBigDecimal(5, ms.getMark());
			if(preStmt.executeUpdate() > 0)
				maxOrder = ms.getOrder() > maxOrder ? ms.getOrder() : maxOrder;
			else
				errMsg += "Marking Scheme: ac_id:"+ ms.getAcId()+ " msOrder:"+ ms.getOrder() +"\r\n";
		}
		preStmt.close();
		
		if(!errMsg.isEmpty()) 
			
			throw new Exception(errMsg);
		if (maxOrder < 1)
			throw new Exception("Incorrect Marking Scheme Order");
		
		preStmt = this.dbCon.preStmt(SQL_DELETE_UNUSED_MS);
		preStmt.setInt(1, msList.get(0).getAcId());
		preStmt.setInt(2, maxOrder);
		
		preStmt.executeUpdate();
		
		preStmt.close();
	}
	
	public ArrayList<AssignmentMaterial> getAssignmentMaterial(int acId) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		
		ArrayList<AssignmentMaterial> listAm = new ArrayList<AssignmentMaterial>();
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_AM);
		//set parameter
		preStmt.setInt(1, acId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		while(rs.next()) {
			int amOrder = rs.getInt("amOrder");
			String name = rs.getString("name");
			String filename = rs.getString("filename");
			String info = rs.getString("information");
			AssignmentMaterial am = new AssignmentMaterial(acId, amOrder, name);
			am.setFileName(filename);
			am.setInfo(info);
			listAm.add(am);
		}
		rs.close();
		preStmt.close();
		return listAm;
	}
	
	public String getAmDownloadPath(int acId, int amOrder) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		if(amOrder < 1)
			throw new Exception("Incorrect Assignment Material Order");
		
		String filePath = MATERIAL_DIR_PATH + "\\"+ acId+ "\\";
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_AM_PATH);
		//set parameter
		preStmt.setInt(1, acId);
		preStmt.setInt(2, amOrder);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		String filename = "";
		if(rs.next()) {
			filename = rs.getString(1);
		}
		rs.close();
		preStmt.close();
		
		if(filename.isEmpty())
			throw new Exception("Can't find the file");
		
		return filePath + filename;
	}
	
	public void updateAssignmentMaterial(ArrayList<AssignmentMaterial> listAm) throws Exception {
		if(listAm == null || listAm.isEmpty())
			throw new Exception("Incorrect Assignment Material list");
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATE_AM);
		preStmt.setInt(1, listAm.get(0).getAcId());
		
		Iterator<AssignmentMaterial> lamI = listAm.iterator();
		String errMsg = "";
		while(lamI.hasNext()) {
			AssignmentMaterial am = lamI.next();
			preStmt.setInt(2, am.getOrder());
			preStmt.setString(3, am.getName());
			preStmt.setString(4, am.getFileName());
			preStmt.setString(5, am.getInfo());
			if(preStmt.executeUpdate() < 1)
				errMsg += "AssignmentMaterial: ac_id:"+ am.getAcId()+ " amOrder:"+ am.getOrder() +"\r\n";
		}
		preStmt.close();
		
		if(!errMsg.isEmpty()) 
			throw new Exception(errMsg);
	}
	
	public void removeUselessAmFile(int acId, ArrayList<Integer> usefulAm) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		if(usefulAm == null || usefulAm.isEmpty())
			throw new Exception("Incorrect list of order");
		
		ArrayList<String> listFile = new ArrayList<String>();
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(regenSQL(SQL_GET_UNUSED_FILE, usefulAm.size()));
		preStmt.setInt(1, acId);
		int i = 2;
		Iterator<Integer> uamI = usefulAm.iterator();
		while(uamI.hasNext()) {
			preStmt.setInt(i, uamI.next());
			i++;
		}
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		while(rs.next()) {
				listFile.add(rs.getString(1));
		}
		rs.close();
		preStmt.close();
		
		//Remove file
		Iterator<String> lfI = listFile.iterator();
		String folderPath = MATERIAL_DIR_PATH + "\\"+ acId+ "\\";
		String errMsg = "";
		while(lfI.hasNext()) {
			String filename = lfI.next();
			if(filename.isEmpty())
				continue;
			//Create fileObj
			File fileObj = new File(folderPath, filename);
			//Check the file is exists or not. If exists, delete it.
			if(fileObj.exists())
				if(!fileObj.delete())
					errMsg += "File: "+ fileObj.getAbsolutePath()+ " Can't be deleted.\r\n"; //Can't delete the old file
		}
		if(!errMsg.isEmpty())
			throw new Exception(errMsg);
		
		//Delete record from database
		preStmt = this.dbCon.preStmt(SQL_DELETE_ALL_AM);
		preStmt.setInt(1, acId);
		//Run SQL
		preStmt.executeUpdate();
		preStmt.close();
	}
	
	public void updateAmFilename(int acId, int amOrder, String filename) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		if(amOrder < 1)
			throw new Exception("Incorrect Assignment Material Order");
		if(checkLength(filename, AssignmentMaterial.FILENAME_MAX_LENGTH))
			throw new Exception("Incorrect File name");
		
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATE_AM_FILE);
		preStmt.setString(1, filename);
		preStmt.setInt(2, acId);
		preStmt.setInt(3, amOrder);
		
		preStmt.executeUpdate();
		preStmt.close();
	}
	
	public void removeAllAm(int acId) throws Exception {
		if(acId < 1)
			throw new Exception("Incorrect Assignment ID");
		
		//Delete record from database
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_DELETE_ALL_AM);
		preStmt.setInt(1, acId);
		//Run SQL
		preStmt.executeUpdate();
		preStmt.close();
		
		File amDir = new File(MATERIAL_DIR_PATH + "\\"+ acId+ "\\");
		if(amDir.exists())
			if(!FileUtils.deleteQuietly(amDir))
				throw new Exception(amDir.getAbsolutePath() + " is not completely deleted.");
	}
}
